﻿// ***********************************************************************
// Assembly         : Tinkerc.Framework.Common
// Author           : Tinkerc
// Created          : 09-20-2014
//
// Last Modified By : Tinkerc
// Last Modified On : 09-20-2014
// ***********************************************************************
// <copyright file="ExcelHelper.cs" company="cry">
//     Copyright (c) cry. All rights reserved.
// </copyright>
// <summary></summary>
// ***********************************************************************
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

/// <summary>
/// The Utility namespace.
/// </summary>
namespace Tinkerc.Framework.Common.Utility
{

    /// <summary>
    /// Class ExcelHelper.
    /// </summary>
    public class ExcelHelper : IDisposable
    {
        /// <summary>
        /// The workbook
        /// </summary>
        private IWorkbook workbook = null;
        /// <summary>
        /// The fs
        /// </summary>
        private FileStream fs = null;
        /// <summary>
        /// The disposed
        /// </summary>
        private bool disposed;

        /// <summary>
        /// 执行与释放或重置非托管资源相关的应用程序定义的任务。
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Releases unmanaged and - optionally - managed resources.
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }

        /// <summary>
        /// 文件流初始化对象
        /// </summary>
        /// <param name="stream">The stream.</param>
        public ExcelHelper(Stream stream)
        {
            _IWorkbook = CreateWorkbook(stream);
        }

        /// <summary>
        /// 传入文件名
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        public ExcelHelper(string fileName)
        {
            disposed = false;
            using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                _IWorkbook = CreateWorkbook(fileStream);
            }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="ExcelHelper"/> class.
        /// </summary>
        public ExcelHelper()
        {
            disposed = false;
        }

        /// <summary>
        /// 工作薄
        /// </summary>
        private IWorkbook _IWorkbook;

        /// <summary>
        /// 创建工作簿对象
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="stream">The stream.</param>
        /// <returns>IWorkbook.</returns>
        private IWorkbook CreateWorkbook(Stream stream)
        {
            try
            {
                return new XSSFWorkbook(stream); //07
            }
            catch
            {
                return new HSSFWorkbook(stream); //03
            }

        }

        /// <summary>
        /// 把Sheet中的数据转换为DataTable
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="sheet">The sheet.</param>
        /// <param name="heads">The heads.</param>
        /// <returns>DataTable.</returns>
        public DataTable ExportToDataTable(ISheet sheet, string[] heads)
        {
            DataTable dt = new DataTable();

            //默认，第一行是字段
            IRow headRow = sheet.GetRow(0);

            // var heads =
            //   "QUOTE_DATE,PRICE,FACTORY_NAME,TOP_CATEGORY_NAME,CATEGORY_NAME,MATERIAL_NAME,SPECIFICATION_NAME".Split(',');

            foreach (string t in heads)
            {
                dt.Columns.Add(t);
            }

            var lastrowNum = sheet.LastRowNum + 1;

            var indexrowNum = sheet.FirstRowNum;// +1;

            try
            {
                //遍历数据行
                for (int i = indexrowNum; i < lastrowNum; i++)
                {
                    IRow tempRow = sheet.GetRow(i);

                    if (tempRow == null) continue;

                    DataRow dataRow = dt.NewRow();

                    var rowStr = "";
                    for (int r = 0, j = tempRow.FirstCellNum, len2 = heads.Length; j < len2; j++, r++)
                    {
                        ICell cell = tempRow.GetCell(j);

                        object cellval = null;

                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {
                                case CellType.String:
                                    cellval = cell.StringCellValue;
                                    break;
                                case CellType.Numeric:
                                    cellval = cell.NumericCellValue;

                                    if (cell.DateCellValue.Year > 2000)
                                    {
                                        cellval = cell.DateCellValue.ToString("yyyy-MM-dd");
                                    }

                                    break;
                                case CellType.Boolean:
                                    cellval = cell.BooleanCellValue;
                                    break;
                                default: dataRow[r] = "";
                                    break;
                            }
                        }

                        dataRow[r] = (cellval + "").Trim();

                        rowStr += cellval + "";
                    }

                    if (!string.IsNullOrWhiteSpace(rowStr))
                    {
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            catch (Exception)
            {

            }

            return dt;
        }

        /// <summary>
        /// Sheet中的数据转换为List集合
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet">The sheet.</param>
        /// <param name="fields">The fields.</param>
        /// <returns>IList&lt;T&gt;.</returns>
        private IList<T> ExportToList<T>(ISheet sheet, string[] fields) where T : class, new()
        {
            IList<T> list = new List<T>();

            //遍历每一行数据
            for (int i = sheet.FirstRowNum + 1, len = sheet.LastRowNum + 1; i < len; i++)
            {
                T t = new T();
                IRow row = sheet.GetRow(i);

                for (int j = 0, len2 = fields.Length; j < len2; j++)
                {
                    ICell cell = row.GetCell(j);
                    object cellValue = null;

                    switch (cell.CellType)
                    {
                        case CellType.String: //文本
                            cellValue = cell.StringCellValue;
                            break;
                        case CellType.Numeric: //数值
                            cellValue = Convert.ToInt32(cell.NumericCellValue); //Double转换为int
                            break;
                        case CellType.Boolean: //bool
                            cellValue = cell.BooleanCellValue;
                            break;
                        case CellType.Blank: //空白
                            cellValue = "";
                            break;
                        default:
                            cellValue = "ERROR";
                            break;
                    }

                    typeof(T).GetProperty(fields[j]).SetValue(t, cellValue, null);
                }
                list.Add(t);
            }

            return list;
        }

        /// <summary>
        /// 获取第一个Sheet的第X行，第Y列的值。起始点为1
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="X">行</param>
        /// <param name="Y">列</param>
        /// <returns>System.String.</returns>
        public string GetCellValue(int X, int Y)
        {
            ISheet sheet = _IWorkbook.GetSheetAt(0);

            IRow row = sheet.GetRow(X - 1);

            return row.GetCell(Y - 1).ToString();
        }

        /// <summary>
        /// 获取一行的所有数据
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="X">第x行</param>
        /// <returns>System.String[].</returns>
        public string[] GetCells(int X)
        {
            List<string> list = new List<string>();

            ISheet sheet = _IWorkbook.GetSheetAt(0);

            IRow row = sheet.GetRow(X - 1);

            for (int i = 0, len = row.LastCellNum; i < len; i++)
            {
                list.Add(row.GetCell(i).StringCellValue); //这里没有考虑数据格式转换，会出现bug
            }
            return list.ToArray();
        }

        /// <summary>
        /// 第一个Sheet数据，转换为DataTable
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="heads">The heads.</param>
        /// <returns>DataTable.</returns>
        public DataTable ExportExcelToDataTable(string[] heads)
        {
            return ExportToDataTable(_IWorkbook.GetSheetAt(0), heads);
        }

        /// <summary>
        /// 第sheetIndex表数据，转换为DataTable
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <param name="sheetIndex">第几个Sheet，从1开始</param>
        /// <param name="heads">The heads.</param>
        /// <returns>DataTable.</returns>
        public DataTable ExportExcelToDataTable(int sheetIndex, string[] heads)
        {
            return ExportToDataTable(_IWorkbook.GetSheetAt(sheetIndex - 1), heads);
        }

        /// <summary>
        /// Excel中默认第一张Sheet导出到集合
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param>
        /// <returns>IList&lt;T&gt;.</returns>
        public IList<T> ExcelToList<T>(string[] fields) where T : class, new()
        {
            return ExportToList<T>(_IWorkbook.GetSheetAt(0), fields);
        }

        /// <summary>
        /// Excel中指定的Sheet导出到集合
        /// Author:Tinkerc
        /// CreateDate: 2014-09-20 23:40:31
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheetIndex">第几张Sheet,从1开始</param>
        /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param>
        /// <returns>IList&lt;T&gt;.</returns>
        public IList<T> ExcelToList<T>(int sheetIndex, string[] fields) where T : class, new()
        {
            return ExportToList<T>(_IWorkbook.GetSheetAt(sheetIndex - 1), fields);
        }

    }
}